﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;

namespace HZ.DrugTrade.DAL
{
    public class DAL_Invoice
    {
        public static int UpdateInvoicePay(HZ.DrugTrade.Model.InvoicePay invoicePay, IList<HZ.DrugTrade.Model.Invoice> invoicelist)
        {
            StringBuilder sbin = new StringBuilder();
            int falg = 0;
            string sql = "";
            for (int i = 0; i < invoicelist.Count; i++)
            {
                 string strsql = string.Format(@"select MODIFYTIME from TB_Invoice where InvoiceSerialID = '{0}'", invoicelist[i].InvoiceSerialID);
                 DateTime time = Convert.ToDateTime(HZ.Data.DbHelperFactory.Create().ExecuteScalar(strsql));
                 if (time.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd") || time == null)
                 {
                     sql = string.Format("UPDATE TB_Invoice SET InvoicePaySerialID = '{1}',MODIFYTIME = sysdate,MODIFYSTATUS =1 WHERE InvoiceSerialID ='{0}'", invoicelist[i].InvoiceSerialID, invoicePay.InvoicePaySerialID);
                 }
                 else
                 {
                     sql = string.Format("UPDATE TB_Invoice SET InvoicePaySerialID = '{1}' WHERE InvoiceSerialID ='{0}'", invoicelist[i].InvoiceSerialID, invoicePay.InvoicePaySerialID);
                 }
                //if (i == 0)
                //{
                //    sbin.AppendFormat("'{0}'", invoicelist[i].InvoiceSerialID);
                //}
                //else
                //{
                //    sbin.AppendFormat(",'{0}'", invoicelist[i].InvoiceSerialID);
                //}
                falg = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
            }
            //string instr = sbin.ToString();

            
            return  falg;
        }
        
        /// <summary>
        /// 获取未开票企业列表
        /// </summary>
        /// <param name="HospitalID">医疗机构OrgID</param>
        /// <returns>企业列表</returns>
        public static IList<HZ.DrugTrade.Model.Company> GetUnInvoiceCompanyList(string HospitalID)
        {
            string sql = string.Format(@"SELECT DISTINCT c.* FROM TB_Distribution t INNER JOIN  TB_PurchaseOrderDetail tp ON t.OrderDetailID = tp.OrderDetailID
INNER JOIN TB_PurchaseOrder t2 ON t2.OrderID = tp.OrderID
INNER JOIN TB_Company c ON tp.CompanyID_PS = c.CompanyID
WHERE t.IsInvoice = 0 AND t2.HospitalID = '{0}'", HospitalID);

            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            //return HZ.Data.DAL_Helper.GetCompaniesFromDataTable(dt);
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.Company>(dt);
             
        }

        /// <summary>
        /// 获取未开票医疗机构列表
        /// </summary>
        /// <param name="CompanyID">企业ID</param>
        /// <returns>医疗机构列表</returns>
        public static IList<Model.Hospital> GetUnInvoiceHospitalList(string CompanyID)
        {
            string sql = string.Format(@"SELECT * FROM TB_Hospital
WHERE HospitalID IN (SELECT HospitalID FROM
View_Distribution WHERE IsInvoice<>1 AND DistributeState='1' AND CompanyID_PS= '{0}')", CompanyID);

            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            //return HZ.Data.DAL_Helper.GetHospitalsFromDataTable(dt);
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.Hospital>(dt);
        }

        /// <summary>
        /// 获取未开票医疗机构列表 zdc
        /// </summary>
        /// <param name="CompanyID">企业ID</param>
        /// <param name="hospitalName">医疗机构名称</param>
        /// <returns>医疗机构列表</returns>
        public static IList<Model.Hospital> GetUnInvoiceHospitalList(string CompanyID,string hospitalName,string where)
        {
            string sql = string.Format(@"select a.*,b.areaname FarAreaName from (SELECT a.*,substr(a.areaid,0,4)||'00' FarAreaid,b.areaname FROM TB_Hospital a inner join tb_area b on a.areaid = b.areaid
WHERE HospitalID IN (SELECT HospitalID FROM
View_Distribution WHERE IsInvoice<>1 and purchasecount > 0  AND DistributeState='1' AND CompanyID_PS= '{0}') and HospitalName like '%{1}%' {2}) a inner join tb_area b on a.FarAreaid  = b.areaid order by a.areaid", CompanyID, hospitalName,where);

            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            //return HZ.Data.DAL_Helper.GetHospitalsFromDataTable(dt);
            return HZ.Data.DAL_Helper.CommonFillList<Model.Hospital>(dt);
        }
        
        public static DataTable GetPurchaseSumQueryByInvoicePay(HZ.DrugTrade.Model.Query.ProcureCatalog where, string InvoicePayID, int currentpage, int pagesize, out int totalrecord)
        {
            string strwhere = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true);
            string sql = string.Format(@"SELECT A.ProductName,A.GoodsID,
	   A.MedicineModelName,
	   A.Outlookc,
	   A.CompanyName_SC,
	   A.Unit,
	   A.GoodsName,
	   A.Factor,
	   A.Pack,
	   A.ProjectName,
	   A.PinyinCode,
	   A.WBCode,B.WarehouseCountSum,
				B.ProcureCatalogID,
				B.Price, B.WarehouseCountSum * B.Price AS WarehouseAmount,B.CompanyID_PS,B.CompanyName_PS FROM View_ProcureCatalog A  INNER JOIN  (
SELECT SUM(td.WarehouseCount) AS WarehouseCountSum , MIN(tpod.ProcureCatalogID) AS ProcureCatalogID, MIN(tpod.Price) AS Price,MIN(c.CompanyID) AS CompanyID_PS, MIN(c.CompanyName) AS CompanyName_PS FROM TB_InvoicePay A INNER JOIN  TB_Invoice AS ti ON A.InvoicePaySerialID = ti.InvoicePaySerialID INNER JOIN  TB_InvoiceDetail AS tid ON ti.InvoiceSerialID = tid.InvoiceSerialID INNER JOIN TB_Distribution AS td ON tid.DistributionSerialID = td.DistributionSerialID INNER JOIN  TB_PurchaseOrderDetail AS tpod ON td.OrderDetailID = tpod.OrderDetailID INNER JOIN TB_Company AS c ON c.CompanyID = tpod.CompanyID_PS
WHERE A.InvoicePaySerialID = '{0}'
GROUP BY tpod.ProcureCatalogID) B ON A.ProcureCatalogID = B.ProcureCatalogID
WHERE 1=1{1}", InvoicePayID, strwhere);

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();

             
        }
    }
}
